// ================================================================================
// 		File: PartyRepresentativeSuggestion.cs
// 		Desc: 
//  
// 		Called by:   
//               
// 		Auth: chenwenzhi（）
// 		Date: 2016-03-23 01:45
// ================================================================================
// 		Change History
// ================================================================================
// 		Date:		Author:				Description:
// 		--------	--------			-------------------
//    
// ================================================================================
// 
// ================================================================================
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using ZHDJ.Core;

namespace ZHDJ.Data.Models.Center
{
    /// <summary>
    /// 党代表心声传递
    /// </summary>   
    public class PartyRepresentativeSuggestion : EntityBase
    {
        #region 属性
        /// <summary>
        /// 心声传递人
        /// </summary>
        public string SuggestionBy { get; set; }

        /// <summary>
        /// 姓名
        /// </summary>
        public string UserName { get; set; }

        /// <summary>
        /// 联系方式
        /// </summary>
        public string MobileNo { get; set; }



        /// <summary>
        /// 年龄
        /// </summary>
        public string Age { get; set; }

        /// <summary>
        /// 性别
        /// </summary>
        public string Sex { get; set; }

        /// <summary>
        /// 业务码
        /// </summary>
        public string SerialNumber { get; set; }

        /// <summary>
        /// 政治面貌
        /// </summary>
        public string PoliticsLineament { get; set; }

        /// <summary>
        /// 工作单位
        /// </summary>
        public string WorkUnit { get; set; }

        /// <summary>
        /// 联系地址
        /// </summary>
        public string Address { get; set; }

        /// <summary>
        /// 邮政编号
        /// </summary>
        public string Zip { get; set; }

        /// <summary>
        /// 职务
        /// </summary>
        public string Post { get; set; }

        /// <summary>
        /// 工作室ID
        /// </summary>
        public string StudioID { get; set; }

        /// <summary>
        /// 党代表ID
        /// </summary>
        public string RepresentativeID { get; set; }

        /// <summary>
        /// 主题
        /// </summary>
        public string Title { get; set; }

        /// <summary>
        /// 是否公开
        /// </summary>
        public bool IsOpen { get; set; }

        /// <summary>
        /// 意见分类
        /// </summary>
        public string SuggestionCategory { get; set; }

        /// <summary>
        /// 反映内容
        /// </summary>
        public string Content { get; set; }

        /// <summary>
        /// 状态（1待处理2正在处理中3已办结）
        /// </summary>
        public int Status { get; set; }

        /// <summary>
        /// 办理建议
        /// </summary>
        public string HandlingType { get; set; }

        /// <summary>
        /// 处理时间
        /// </summary>
        public DateTime? HandlingTime { get; set; }

        /// <summary>
        /// 办结时间
        /// </summary>
        public DateTime? FinishTime { get; set; }

        /// <summary>
        /// 回复内容
        /// </summary>
        public string Reply { get; set; }

        /// <summary>
        /// 创建用户
        /// </summary>
        public string CreateBy { get; set; }

        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTime? CreateTime { get; set; }

        /// <summary>
        /// 更新时间
        /// </summary>
        public DateTime? ModifyTime { get; set; }

        /// <summary>
        /// 修改用户
        /// </summary>
        public string ModifyBy { get; set; }

        /// <summary>
        /// 评价（满意，不满意）
        /// </summary>
        public string Evaluation { get; set; }

        #endregion

        #region 查询管理

        public static DataSet GetGridData(int pageIndex, int pageSize, string status, string searchKey = "",
            string studioID = "", string representativeId = "", string isOpen = "", string loginId = "")
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "Begin",Value = (pageIndex)*pageSize},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "PageSize",Value = pageSize},
                 new MySqlParameter(){DbType = DbType.String,ParameterName = "SuggestionCategoryT",Value = SysDataDictionary.PartyRepresentativeSuggestionCategory},
                 new MySqlParameter(){DbType = DbType.String,ParameterName = "HandlingTypeT",Value = SysDataDictionary.PartyRepresentativeHandlingType},
            };


            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  data.ID,data.UserName,data.MobileNo,data.Age,data.SerialNumber,data.SuggestionBy,
                        data.PoliticsLineament,data.WorkUnit,data.Address,data.Zip,data.Post,data.StudioID,
                        data.RepresentativeID,data.Title,data.IsOpen,data.SuggestionCategory,data.Content,
                        data.Status,data.HandlingTime,data.FinishTime,data.Reply,data.CreateBy,data.CreateTime,
                        data.ModifyTime,data.ModifyBy,data.Evaluation,
                        case data.Status when 2 then '正在处理' when 3 then '已办结'  else '待处理' end as  StatusName,
                        pt.UserName as RepresentativeName,st.StudioName,
                        dd.DDicMemberName as SuggestionCategoryName,dd2.DDicMemberName as HandlingTypeName,m.UserName as SuggestionByName
                        from tblPartyRepresentativeSuggestion data
                        inner join tblPartyRepresentativeInfo pt on (pt.ID=data.RepresentativeID)
                        inner join tblPartyRepresentativeStudio st on (st.ID=data.StudioID)
                        left join tbldatadictionary dd on (dd.DDicType=?SuggestionCategoryT and data.SuggestionCategory=dd.DDicMember)
                        left join tbldatadictionary dd2 on (dd2.DDicType=?HandlingTypeT and data.HandlingType=dd2.DDicMember)
inner join tblPartyOrgMember m on (m.LoginID=data.SuggestionBy)
                        where 1=1 ");
            if (!string.IsNullOrEmpty(searchKey))
            {
                sb.Append(" and data.Title like ?key ");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "key", Value = string.Format("%{0}%", searchKey) });
            }
            if (!string.IsNullOrEmpty(status))
            {
                sb.Append(" and data.status=?status");
                parameters.Add(new MySqlParameter() { DbType = DbType.Int32, ParameterName = "status", Value = status });
            }
            if (!string.IsNullOrEmpty(isOpen))
            {
                sb.Append(" and data.isOpen=?isOpen");
                parameters.Add(new MySqlParameter() { DbType = DbType.Int32, ParameterName = "isOpen", Value = isOpen });
            }
            if (!string.IsNullOrEmpty(studioID))
            {
                sb.Append(" and data.studioID=?studioID ");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "studioID", Value = studioID });
            }
            if (!string.IsNullOrEmpty(representativeId))
            {
                sb.Append(" and data.RepresentativeID=?representativeId  ");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "representativeId", Value = representativeId });
            }
            if (!string.IsNullOrEmpty(loginId))
            {
                sb.Append(" and data.SuggestionBy=?SuggestionBy  ");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "SuggestionBy", Value = loginId });
            }
            sb.Append(" order by data.CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }

        public static DataSet GetOne(string id)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  data.ID,data.UserName,data.MobileNo,data.Age,data.SerialNumber,data.SuggestionBy,
                        data.PoliticsLineament,data.WorkUnit,data.Address,data.Zip,data.Post,data.StudioID,
                        data.RepresentativeID,data.Title,data.IsOpen,data.SuggestionCategory,data.Content,
                        data.Status,data.HandlingTime,data.FinishTime,data.Reply,data.CreateBy,data.CreateTime,
                        data.ModifyTime,data.ModifyBy,data.Evaluation,
                        case data.Status when 2 then '正在处理' when 3 then '已办结'  else '待处理' end as  StatusName,
                        pt.UserName as RepresentativeName,st.StudioName,
                        dd.DDicMemberName as SuggestionCategoryName,dd2.DDicMemberName as HandlingTypeName  
                        from tblPartyRepresentativeSuggestion data
                        inner join tblPartyRepresentativeInfo pt on (pt.ID=data.RepresentativeID)
                        inner join tblPartyRepresentativeStudio st on (st.ID=data.StudioID)
                        left join tbldatadictionary dd on (dd.DDicType=?SuggestionCategoryT and data.SuggestionCategory=dd.DDicMember)
                        left join tbldatadictionary dd2 on (dd2.DDicType=?HandlingTypeT and data.HandlingType=dd2.DDicMember)
                        where data.ID=?id ");
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.String,ParameterName = "id",Value = id},
                 new MySqlParameter(){DbType = DbType.String,ParameterName = "SuggestionCategoryT",Value = SysDataDictionary.PartyRepresentativeSuggestionCategory},
                 new MySqlParameter(){DbType = DbType.String,ParameterName = "HandlingTypeT",Value = SysDataDictionary.PartyRepresentativeHandlingType},
            };
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }


        #endregion
    }
}
